﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using Data;

namespace prjCompras
{
    public partial class frmProcGerarCartaCotacao : FormProcessoPadrao.FormProcessoPadrao
    {
        public frmProcGerarCartaCotacao()
        {
            InitializeComponent();
        }

        List<String> fornecedores = new List<String>();

        private void frmProcGerarCartaCotacao_Load(object sender, EventArgs e)
        {
            pnlFimProcesso.Visible = false;
            tbcntrlEtapas.Visible = false;
            pnlFimProcesso.Dock = DockStyle.Fill;
            pnlInicio.Dock = DockStyle.Fill;
            insereTextoProcesso(" Gerar Carta de Cotação");
            carregaGrid();
        }

        private void carregaGrid()
        {
            string campos = "Codigo, Data, Case when Urgente = 0 then 'Não' when Urgente = 1 then 'Sim' end as Urgente";
            string sql = "Select " + campos + " from " + ConstTabelas.REQUISIÇÃO + " where Tipo = 1 and CartaGerada = 0";
            dtgrdvwPlano.DataSource = Conexao.getInstance.executeQuery(sql);/*
            DataGridViewCheckBoxColumn check = new DataGridViewCheckBoxColumn();
            check.FalseValue = false;
            check.TrueValue = true;
            check.HeaderCell.Style.Alignment = DataGridViewContentAlignment.MiddleCenter;
            check.HeaderText = "[x]";
            dtgrdvwPlano.Columns.Add(check);
            zerarColunaCheck();*/
        }

        private void dtgrdvwPlano_SelectionChanged(object sender, EventArgs e)
        {
            string campos = "M.Codigo, M.Nome, RM.Quantidade";
            string sql = "Select " + campos + " from " + ConstTabelas.REQUISICAOMATERIAL + " as RM, " + ConstTabelas.MATERIAL + " as M where"
                + " RM.CodigoMaterial = M.Codigo and RM.CodigoRequisicao = " + dtgrdvwPlano.CurrentRow.Cells[0].Value.ToString();
            if (dtgrdvwPlano.RowCount > 0)
            {
                dtgrdvwMateriais.DataSource = Conexao.getInstance.executeQuery(sql);
            }

            atualizarListaFornecedores();
            preencheGridFornecedor();
        }

        private void dtgrdvwPlano_CellDoubleClick(object sender, DataGridViewCellEventArgs e)
        {
            /*if ((bool)dtgrdvwPlano.CurrentRow.Cells[3].Value == true)
            {
                dtgrdvwPlano.CurrentRow.Cells[3].Value = false;
            }
            else
            {
                dtgrdvwPlano.CurrentRow.Cells[3].Value = true;
            }
            atualizarListaFornecedores();
            preencheGridFornecedor();*/
        }

        private void zerarColunaCheck()
        {
            foreach (DataGridViewRow r in dtgrdvwPlano.Rows)
            {
                r.Cells[3].Value = false;
            }
        }

        private void atualizarListaFornecedores()
        {
            fornecedores.Clear();
            dtgrdvwFornecedores.DataSource = null;
            //int index = dtgrdvwPlano.CurrentRow.Index;
            string materiais = "";
            //int i = 0;
            /*foreach (DataGridViewRow r in dtgrdvwPlano.Rows)
            {
                if ((bool)r.Cells[3].Value == true)
                {
                    dtgrdvwPlano.CurrentCell = dtgrdvwPlano[0, i];
                    dtgrdvwPlano_SelectionChanged(null, null);*/

            foreach (DataGridViewRow r2 in dtgrdvwMateriais.Rows)
            {
                if (materiais != "")
                {
                    materiais += ", ";
                }
                materiais += "'" + r2.Cells[0].Value.ToString() + "'";
            }
            /*
            }
            i++;
        }*/
            //dtgrdvwPlano.CurrentCell = dtgrdvwPlano[0, index];
            if (materiais == "")
            {
                return;
            }

            string sql = "Select f.CNPJ, f.Nome from " + ConstTabelas.FORNECEDOR + " f, " + ConstTabelas.FORNECEDORMATERIAL + " fm " +
                "where f.CNPJ = fm.CNPJFornecedor and fm.CodigoMaterial in (" + materiais + ")";

            DataTable dt = Conexao.getInstance.executeQuery(sql);
            foreach (DataRow r in dt.Rows)
            {
                if (!fornecedores.Contains(r[0].ToString()))
                {
                    fornecedores.Add(r[0].ToString());
                }
            }
        }

        private void preencheGridFornecedor()
        {
            string forn = "";
            foreach (string f in fornecedores)
            {
                if (forn != "")
                {
                    forn += ", ";
                }
                forn += "'" + f + "'";
            }

            if (forn == "")
            {
                return;
            }

            string sql = "Select CNPJ, Nome from " + ConstTabelas.FORNECEDOR + " where CNPJ in (" + forn + ")";
            dtgrdvwFornecedores.DataSource = Conexao.getInstance.executeQuery(sql);
        }

        public override bool metodo()
        {
            if (dtgrdvwFornecedores.RowCount == 0)
            {
                MessageBox.Show("Nenhum fornecedor informado!", "Erro!", MessageBoxButtons.OK, MessageBoxIcon.Warning);
                return false;
            }

            //int i = 0;
            List<string> materais = new List<string>();
            List<string> req = new List<string>();/*
            foreach (DataGridViewRow r in dtgrdvwPlano.Rows)
            {
                if ((bool)r.Cells[3].Value == true)
                {
                    dtgrdvwPlano.CurrentCell = dtgrdvwPlano[0, i];
                    dtgrdvwPlano_SelectionChanged(null, null);

                    req.Add(r.Cells[0].Value.ToString());*/

            foreach (DataGridViewRow r2 in dtgrdvwMateriais.Rows)
            {
                if (!materais.Contains(r2.Cells[0].Value.ToString()))
                {
                    materais.Add(r2.Cells[0].Value.ToString());
                }
            }/*
                }
                i++;
            }*/

            string cod_carta;

            foreach (string f in fornecedores)
            {
                List<String> campos = new List<string>();
                List<object> valores = new List<object>();

                campos.Add("CNPJFornecedor");
                campos.Add("Data");

                valores.Add(f);
                valores.Add(System.DateTime.Now.ToShortDateString());
                string sql = SqlCrud.getInstance.insert(ConstTabelas.CARTACOTACAO, campos, valores);
                if (!Conexao.getInstance.execute(sql, false))
                {
                    return false;
                }

                sql = SqlCrud.getInstance.selectMax(ConstTabelas.CARTACOTACAO);
                DataTable dt = Conexao.getInstance.executeQuery(sql);
                DataRow dr = dt.Rows[0];
                cod_carta = dr[0].ToString();

                campos.Clear();

                campos.Add("CodigoCartaCotacao");
                campos.Add("CodigoMaterial");
                campos.Add("Quantidade");

                string reqs = "";/*
                foreach (string r in req)
                {
                    if (reqs != "")
                    {
                        reqs += ", ";
                    }
                    reqs += r;
                }*/

                reqs = dtgrdvwPlano.CurrentRow.Cells[0].Value.ToString();

                foreach (string m in materais)
                {
                    string qtde = "";
                    sql = @"select SUM(rm.Quantidade) qtde
                              from Requisicao r,
		                            RequisicaoMaterial rm
	                            where r.Codigo = rm.CodigoRequisicao
	                              and r.Codigo in (" + reqs + @")
                                  and rm.CodigoMaterial = " + m + @"
                            group by rm.CodigoMaterial";

                    DataTable dt2 = Conexao.getInstance.executeQuery(sql);
                    DataRow r = dt2.Rows[0];

                    qtde = r[0].ToString();

                    dt2 = null;
                    sql = "Select * from FornecedorMaterial where CNPJFornecedor = '" + f + "' and CodigoMaterial = " + m;
                    dt2 = Conexao.getInstance.executeQuery(sql);
                    if (dt2.Rows.Count > 0)
                    {
                        valores.Clear();
                        valores.Add(cod_carta);
                        valores.Add(m);
                        valores.Add(qtde);

                        sql = SqlCrud.getInstance.insert(ConstTabelas.CARTACOTACAOMATERIAL, campos, valores);

                        if (!Conexao.getInstance.execute(sql, false))
                        {
                            return false;
                        }
                    }
                }
                /*
                foreach (DataGridViewRow r in dtgrdvwPlano.Rows)
                {
                    if ((bool)r.Cells[3].Value == true)
                    {*/
                List<string> alt = new List<string>();
                alt.Add("CartaGerada = 1");
                sql = SqlCrud.getInstance.updateById(ConstTabelas.REQUISIÇÃO, alt, ConstTabelas.REQUISIÇÃO_PK, reqs);
                if (!Conexao.getInstance.execute(sql, false))
                {
                    return false;
                }/*
                    }
                }*/

                alt = new List<string>();
                alt.Add("CodigoRequisicao = " + reqs);
                sql = SqlCrud.getInstance.updateById(ConstTabelas.CARTACOTACAO, alt, ConstTabelas.CARTACOTACAO_PK, cod_carta);
                if (!Conexao.getInstance.execute(sql, false))
                {
                    return false;
                }

                Conexao.getInstance.CommitTransacao();
            }

            return true;
        }
    }
}
